﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ModelLayer;

namespace DBLayer
{
    public class CinemaDBClient : ICinemaDB
    {
        private static SqlCommand dbCmd;

        public Cinema GetCinema(int id)
        {
            string sql = "SELECT id, name, address FROM Cinema WHERE id = " + id + " ORDER BY id ASC;";
            
            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader = dbCmd.ExecuteReader();

            Cinema cinema;
            if (dbReader.Read())
            {
                cinema = new Cinema(Convert.ToInt32(dbReader["id"].ToString()), dbReader["name"].ToString(), dbReader["address"].ToString());
            }
            else
            {
                cinema = null;
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return cinema;
        }

        public List<Cinema> GetCinemas()
        {
            string sql = "SELECT id, name, address FROM Cinema ORDER BY id ASC;";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader = dbCmd.ExecuteReader();

            List<Cinema> cinemas = new List<Cinema>();
            while (dbReader.Read())
            {
                cinemas.Add(new Cinema(Convert.ToInt32(dbReader["id"].ToString()), dbReader["name"].ToString(), dbReader["address"].ToString()));
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return cinemas;
        }

        public int AddCinema(Cinema cinema)
        {
            string sql = "INSERT INTO Cinema (name, address) VALUES ('" + cinema.Name + "', '" + cinema.Address + "');";

            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public int EditCinema(Cinema cinema)
        {
            string sql = "UPDATE Cinema SET name = '" + cinema.Name + "', address = '" + cinema.Address + "' WHERE id = " + cinema.Id + ";";

            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public int RemoveCinema(int id)
        {
            string sql = "DELETE FROM Cinema WHERE id = " + id + ";";

            return AccessDBSQLClient.ExecuteQuery(sql);
        }
    }
}
